﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_AddPostil_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_AddPostil_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_AddPostil_V2]
    @DocID int,
    @Postiler nvarchar(300),
    @PostilContent ntext,
    @PostilType int,
    @ObjID int =0,
    @ObjType int = 0
    
AS
BEGIN
SET NOCOUNT ON;

DECLARE @staff_id int;
DECLARE @StepID  int;

DECLARE @DefineID int

-- get the id of the specified user.
SELECT @staff_id = staff_id FROM dbo.uds_staff WHERE staff_name = @Postiler;
IF @@rowcount <> 1
BEGIN
	raiserror(N'The specified user "%s" cannot be found.', 16, 1, @Postiler);
    return -1;
END;

IF EXISTS(SELECT 1 FROM dbo.UDS_Flow_Document WHERE Doc_ID = @DocID)
BEGIN

    SELECT @StepID = Step_ID FROM dbo.uds_flow_document WHERE doc_id = @DocID;

    INSERT INTO dbo.UDS_Flow_Postil (Doc_ID, Step_ID, Postiler_ID, Postil_Date, Postil_Content, Postil_Type, Obj_ID, obj_type)
        VALUES(@DocID, @StepID, @staff_id, getdate(), @PostilContent, @PostilType, @ObjID, @objtype);

    RETURN @@IDENTITY;
END

-- The specified document does not exist!
RETURN -1;
END
GO
